﻿using System;
using System.Data;
using System.Data.SqlClient;
using BoutiquePOS.DAL.DAO;

namespace BoutiquePOS.DAL.Gateway
{
    public class CustomerPaymentGateway : DbGateway
    {
        public string SaveCustomerPayment(CustomerPayment aCustomerPayment)
        {
            try
            {
                ConnectionObj.Open();
                string query = String.Format("INSERT INTO PosCustomerPayment VALUES( @salesId,@paymentDate,@paidAmount)");
                CommandObj.Parameters.Clear();
                CommandObj.CommandText = query;
                CommandObj.Parameters.Add("@salesId", SqlDbType.Int);
                CommandObj.Parameters["@salesId"].Value = aCustomerPayment.ASales.SalesId;
                CommandObj.Parameters.Add("@paymentDate", SqlDbType.VarChar);
                CommandObj.Parameters["@paymentDate"].Value = aCustomerPayment.PaymentDate;
                CommandObj.Parameters.Add("@paidAmount", SqlDbType.Decimal);
                CommandObj.Parameters["@paidAmount"].Value = aCustomerPayment.PaidAmount;
                CommandObj.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception("Company couldn't saved", ex);
            }
            finally
            {
                if (ConnectionObj != null && ConnectionObj.State == ConnectionState.Open)
                {
                    ConnectionObj.Close();
                }
            }
            return "Save successfully";
        }

        public string DeleteCustomerPaymentBySalesId(int customerPaymentId)
        {
            int i = 0;
            try
            {
                ConnectionObj.Open();
                string delete = String.Format("DELETE FROM PosCustomerPayment WHERE customerPaymentId='{0}'", customerPaymentId);
                CommandObj.CommandText = delete;
                i = CommandObj.ExecuteNonQuery();
            }
            catch (Exception)
            {
                throw new Exception("Database Error");
            }
            finally
            {
                if (ConnectionObj != null && ConnectionObj.State == ConnectionState.Open)
                {
                    ConnectionObj.Close();
                }
            }
            if (i != 0)
            {
                return "Delete Successfully";
            }
            else
            {
                return "Customer Payment Id does not found";
            }
        }

        public decimal GetDuesAmount(int SalesId)
        {
            decimal totalNetAmt = 0;
            decimal totalReturnAmt = 0;
            decimal totalPaymentAmt = 0;

            try
            {
                ConnectionObj.Open();
                string query1 = String.Format("select totalNetAmount from PosSales where salesId='{0}'", SalesId);

                CommandObj.CommandText = query1;
                SqlDataReader reader = CommandObj.ExecuteReader();
                while (reader.Read())
                {
                    totalNetAmt = Convert.ToDecimal(reader[0]);
                }
                reader.Close();
                ConnectionObj.Close();
                ConnectionObj.Open();
                string query2 = String.Format("SELECT SUM(totalNetAmount) AS totalNetAmount FROM PosSalesReturn where salesId='{0}'", SalesId);

                CommandObj.CommandText = query2;
                reader = CommandObj.ExecuteReader();
                while (reader.Read())
                {
                    string msg = Convert.ToString(reader[0]);
                    if (msg != "")
                    {
                        totalReturnAmt = Convert.ToDecimal(reader[0]);
                    }
                }
                reader.Close();
                ConnectionObj.Close();
                ConnectionObj.Open();
                string query3 = String.Format("SELECT SUM(paidAmount) AS paidAmount FROM PosCustomerPayment where salesId='{0}'", SalesId);

                CommandObj.CommandText = query3;
                reader = CommandObj.ExecuteReader();
                while (reader.Read())
                {
                    string msg = Convert.ToString(reader[0]);
                    if (msg != "")
                    {
                        totalPaymentAmt = Convert.ToDecimal(reader[0]);
                    }
                }
                reader.Close();
                ConnectionObj.Close();
            }
            catch (Exception ex)
            {
                throw new Exception("Error", ex);
            }
            return (totalNetAmt - totalReturnAmt - totalPaymentAmt);
        }

    }
}